package com.topway.dsmflex.thread.services;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.topway.dsmflex.Log;
import com.topway.dsmflex.model.synchro.DataFileBean;
import com.topway.dsmflex.model.synchro.DataTableBean;
import com.topway.dsmflex.thread.handler.DataHandle;
import com.topway.dsmflex.util.StringUtil;

/**
 * 文件同步
 * 
 * @author liusy 2013-07-17
 * 
 */
public class FileSynchroServices extends AbstractSynchroServices {

	public FileSynchroServices() {

	}

	private static ThreadLocal<FileSynchroServices> synchroDao = new ThreadLocal<FileSynchroServices>();

	public synchronized static FileSynchroServices getSynchroDao() {
		if (null == synchroDao.get()) {
			synchroDao.set(new FileSynchroServices());
		}
		return synchroDao.get();
	}
	private DataFileBean dt;
	
	private InputStreamReader inputReader = null;
	private BufferedReader bufferReader = null;
	private InputStream inputStream  = null;
	private Map<String,String> _map = null;
	private String line = null;
	private String [] rowArray = null;
	private int rownum = 0;
	private List<Map<String, String>> _mapList = null;
	private int countNum = 0;

	 @Override
	public int synchroData(Connection sourceConn, Connection targetConn, DataTableBean dt) throws SQLException {
		this.dt = (DataFileBean)dt;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSetMetaData rsmd = null;
		Map<String, Integer> mapColumnType = new HashMap<String, Integer>();
		try {
			stmt = targetConn.createStatement();
			final String fileType = this.dt.getFileType();
				rs = stmt.executeQuery("select "+dt.ListConvertString(dt.getColumnTarget())+" from "+dt.getTableNameTarget()+" where 1<>1");
				if (null == rsmd) {
					rsmd = rs.getMetaData();
					int count = rsmd.getColumnCount();
					for (int k = 0; k < count ; k++) {
						mapColumnType.put("" + k, rsmd.getColumnType(k + 1));
					}
				}

				FileHandler(sourceConn, targetConn, this.dt, mapColumnType, this.dt.getSourceFilePath(), fileType);

		} catch (Exception e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} finally {
			if (null != dataHandle)
				dataHandle.close();
			dataHandle = null;
			if (null != rs)
				rs.close();
			if (null != stmt)
				stmt.close();
			// System.gc();
		}
		return countNum;
	}

	
	
	
	public void FileHandler(Connection sourceConn, Connection targetConn,
			DataFileBean dt,Map<String, Integer> mapColumnType,String filePath, final String fileType) {
		File root = new File(filePath);
		if(!root.exists()){
			Log.info("文件目录不存在！！任务［"+dt.getName()+"］中文件［"+filePath+"］");
			return;
		}
				File[] files = root.listFiles(new FileFilter() {
			@Override
			public boolean accept(File pathname) {
				// 判断文件名是目录 或 .xls 结尾
				if (pathname.isDirectory()) {
					return true;
				} else if(pathname.getName().toUpperCase().endsWith(fileType.toUpperCase()))
				{
					return true;
				}else if(fileType.toUpperCase().equals("EXCEL"))
				{
					if(pathname.getName().toUpperCase().endsWith(".XLS")||pathname.getName().toUpperCase().endsWith(".XLSX"))
							return true;
				}
				return false;
			}
		});
		for (File file : files) {
			if (file.isDirectory()) {
				FileHandler(sourceConn, targetConn, dt, mapColumnType, filePath, fileType);
			} else {
				//处理
				countNum=+listMap.size();
				lobMap.clear();
				listMap.clear();
				
				
				
				
				if(file.getName().toUpperCase().endsWith(".TXT"))
				{
					listMap = getListMap(file);
				}
				else if(file.getName().toUpperCase().endsWith(".XLS")){
					listMap =readXls(file);
				}else if(file.getName().toUpperCase().endsWith(".XLSX"))
				{
					listMap =extractTextFromXLS2007(file);
				}
				
				
				
				if (listMap.size() != 0) {
					if (null == dataHandle)
						dataHandle = new DataHandle(mapColumnType, targetConn, sourceConn, dt, listMap);
					dataHandle.exeDataHandle(listMap, lobMap);
					if(null!=dt.getTargetExecuteSql()&&dt.getTargetExecuteSql().length()>0)
						try {
							updateTableState(targetConn, dt.getTargetExecuteSql());
						} catch (SQLException e) {
							Log.info("执行操作后数据异常！！！任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］"+ dt.getTargetExecuteSql());
						}
				}

				if(null!=dt.getSourceFileSavePath()&&dt.getSourceFileSavePath().length()>0)
				    file.renameTo(new File(dt.getSourceFileSavePath(), file.getName()));
				if(dt.getOperatorSource().equals("DELETE"))
					file.delete();
				
			}
		}
	}

	public List<Map<String, String>> getListMap(File file) {
		_mapList = new ArrayList<Map<String,String>>();
		rownum = 0;
		try {
			inputStream = new FileInputStream(file);
			inputReader = new InputStreamReader(inputStream);
			bufferReader = new BufferedReader(inputReader);
			while ((line = bufferReader.readLine()) != null) {
				_map = new HashMap<String,String>();
				rowArray = line.split(dt.getSeparate());
				if(rowArray.length>=dt.getColumnTarget().size())
				{
					for (int i = 0; i < rowArray.length; i++) {
						_map.put(""+i, rowArray[i]);
					}
					_mapList.add(_map);
				}
				else
				{
					Log.info("无效数据！！忽略！任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］中的［"+rownum+"］行数据："+line);
				}
				rownum++;
			}
		} catch (IOException e) {
			Log.error("文件取数异常！！！"+e.getMessage()+" 任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］中的［"+rownum+"］行数据："+line);
			Log.error("忽略 ！！ 任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］,进入下个文件");
		} finally {
			try {
				if(null!=inputStream)
				inputStream.close();
				if(null!=inputReader)
				inputReader.close();
				if(null!=bufferReader)
				bufferReader.close();
			} catch (IOException e) {
				Log.error("文件关闭异常！！！"+e.getMessage()+"任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］中");
				Log.error("忽略 ！！ 任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］,进入下个文件");
			}
	
		}
		return _mapList;
	}
	

	
	private DecimalFormat df0 = new DecimalFormat("#,##0.00");
	private DecimalFormat df1 = new DecimalFormat("#######################");
	private HSSFWorkbook workbook = null;
	private HSSFSheet sheet =null;
	private HSSFRow row  = null;
	private HSSFCell cell  = null;
	private Date dateCell = null;
	
	 
	 public List<Map<String,String>> readXls(File f)  {
	      try {
	    	  _mapList = new ArrayList<Map<String,String>>();
	 		inputStream = new FileInputStream(f);
	      workbook = new HSSFWorkbook(inputStream);
	      int a = workbook.getNumberOfSheets();
	      for (int i = 0; i < a; i++) {
	          sheet = workbook.getSheetAt(i);
	         if(null==sheet)continue;
	         int rows = sheet.getPhysicalNumberOfRows();
	         
	     	row =sheet.getRow(0);
			if(null!=xRow)
				lastCellNum =row.getLastCellNum();
			else{
				Log.error("首行必须有数据，列数以首行为准。。。 任务［"+dt.getName()+"］中文件［"+f.getAbsolutePath()+"］中 sheet:"+i);
				continue;
			}
		    if(lastCellNum<dt.getColumnTarget().size())
            {
            	Log.info("无效数据！！数据列与目标表列数据 不对应！任务［"+dt.getName()+"］中文件［"+f.getAbsolutePath()+"］中第［"+i+"］个shell ");
            	continue;
            }
			  
			  
	         for (int r = Integer.parseInt(dt.getStartRow()); r < rows; r++) {
	            row = sheet.getRow(r);
	            if(null==row)continue;
	        //    int cells = row.getLastCellNum();
	        
	            _map = new HashMap<String,String>();
	            cellNumCount=0;
	            for (int c = 0; c < lastCellNum; c++) {
	               cell = row.getCell(c);
	               if (cell == null) 
	               {
	            	   cellNumCount++;
	            	   _map.put("" + c,"");
	            	   continue;
	               }
	            	   try {
				                  switch (cell.getCellType()) {
				                  
				                     case HSSFCell.CELL_TYPE_FORMULA:
				                         if (!cell.getStringCellValue().equals("")) {
				                             _map.put("" + c, cell.getStringCellValue());
				                          } else {
				                        	  _map.put("" + c, "");
				                          }
				                        break;
				                     case HSSFCell.CELL_TYPE_NUMERIC:
				                    	   if (HSSFDateUtil.isCellDateFormatted(cell)) {
				                    		   dateCell = cell.getDateCellValue();
				                               if (dateCell != null) {
				                            	   _map.put("" + c,  StringUtil.dateToString(dateCell));
				                               } else {
				                            	   _map.put("" + c,  "");
				                               }
				                            } else {
				                            	try {
													if ((cell.getNumericCellValue() + "").indexOf(".") == -1) {
														_map.put("" + c, df0.format(cell.getNumericCellValue()));
													} else {
														_map.put("" + c, df1.format(cell.getNumericCellValue()));
													}
												} catch (Exception e) {
													_map.put("" + c, cell.getStringCellValue().trim());
												}
				                            }
				                        break;
				                     case HSSFCell.CELL_TYPE_STRING:
				                    	 _map.put("" + c, cell.getStringCellValue().trim());
				                        break;
				                     case HSSFCell.CELL_TYPE_BLANK:
				                    	 _map.put("" + c, "");
				                         break;
				                     case HSSFCell.CELL_TYPE_ERROR:
				                    	 _map.put("" + c, "");
				                         break;
				                     case HSSFCell.CELL_TYPE_BOOLEAN:
				                         _map.put("" + c, (cell.getBooleanCellValue() == true ? "1": "0"));
				                         break;
				                     default:
				                    	 _map.put("" + c, cell.getStringCellValue().trim());
				                  }
	      			
						} catch (Exception e) {
							Log.info("无效数据！！忽略！任务［"+dt.getName()+"］中文件［"+f.getAbsolutePath()+"］中第［"+i+"］个shell 的［"+r+"］行数据,列："+c);
							 _map.put("" + c,"");
						}
	            }
	            if(lastCellNum!=cellNumCount)
	            _mapList.add(_map);
	         }
	      }			
			} catch (Exception e) {
				Log.error("读取文件异常！！"+e.getMessage()+"任务［"+dt.getName()+"］中文件［"+f.getAbsolutePath()+"］中");
				Log.error("忽略 ！！ 任务［"+dt.getName()+"］中文件［"+f.getAbsolutePath()+"］,进入下个文件");
			}finally {
				try {
					workbook = null;
					 sheet =null;
					 row  = null;
					 cell  = null;
					if(null!=inputStream)
					inputStream.close();
					if(null!=inputReader)
					inputReader.close();
					if(null!=bufferReader)
					bufferReader.close();
				} catch (IOException e) {
					Log.error("文件关闭异常！！！"+e.getMessage()+"任务［"+dt.getName()+"］中文件［"+f.getAbsolutePath()+"］中");
					Log.error("忽略 ！！ 任务［"+dt.getName()+"］中文件［"+f.getAbsolutePath()+"］,进入下个文件");
				}
		
			}
	      return _mapList;
	   }

		XSSFWorkbook xwb = null;
		XSSFRow xRow = null;
		int lastCellNum = 0;
		int cellNumCount=0;
	public   List<Map<String,String>> extractTextFromXLS2007(File file) {

		try{
			inputStream = new FileInputStream(file);
			xwb= new XSSFWorkbook(inputStream);
			_mapList = new ArrayList<Map<String,String>>();
		for (int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++) {
			XSSFSheet xSheet = xwb.getSheetAt(numSheet);
			if (xSheet == null) {
				continue;
			}

			
			xRow =xSheet.getRow(0);
			if(null!=xRow)
				lastCellNum =xRow.getLastCellNum();
			else{
				Log.error("首行必须有数据，列数以首行为准。。。 任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］中 sheet:"+numSheet);
				continue;
			}
			  if(lastCellNum<(dt.getColumnTarget().size()))
	            {
	            	Log.info("无效数据！！数据列与目标表列数据 不对应！任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］中第［"+numSheet+"］个shell");
	            	continue;
	            }
			  
			  
			
			for (int rowNum = Integer.parseInt(dt.getStartRow()); rowNum <= xSheet.getLastRowNum(); rowNum++) {
				xRow = xSheet.getRow(rowNum);
				if (xRow == null) {
					continue;
				}
			
		
					_map = new HashMap<String,String>();
					cellNumCount=0;
				for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
					XSSFCell xCell = xRow.getCell(cellNum);
					if (xCell == null) {
						cellNumCount++;
						 _map.put("" + cellNum,"");
					}else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
						 _map.put("" + cellNum, (xCell.getBooleanCellValue() == true ? "1": "0"));
					} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
						   if (HSSFDateUtil.isCellDateFormatted(xCell)) {
                    		   dateCell = xCell.getDateCellValue();
                               if (dateCell != null) {
                            	   _map.put("" + cellNum,  StringUtil.dateToString(dateCell));
                               } else {
                            	   _map.put("" + cellNum,  "");
                               }
                            } else {
                            	try {
        							if ((xCell.getNumericCellValue() + "").indexOf(".") == -1) {
        					
        								_map.put("" + cellNum, df0.format(xCell.getNumericCellValue()));
        							} else {
        								_map.put("" + cellNum, df1.format(xCell.getNumericCellValue()));
        							}
        						} catch (Exception e) {
        							 _map.put("" + cellNum,"");
        							 e.printStackTrace();
        						}
                            }
						
					} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
						 _map.put("" + cellNum,xCell.getStringCellValue());
					} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
						 _map.put("" +cellNum,"");
					} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_ERROR) {
						 _map.put("" + cellNum,"");
					}else if (xCell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
						 if (!xCell.getStringCellValue().equals("")) {
	                         _map.put("" +cellNum, xCell.getStringCellValue());
	                      } else {
	                    	  _map.put("" + cellNum, "");
	                      }
					}
					else
					{
						 _map.put("" + cellNum,"");
					}
				}
				if(lastCellNum!=cellNumCount)
				_mapList.add(_map);
			}
		}
		} catch (Exception e) {
			Log.error("读取文件异常！！"+e.getMessage()+"任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］中");
			Log.error("忽略 ！！ 任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］,进入下个文件");
		}finally {
			try {
				workbook = null;
				 sheet =null;
				 row  = null;
				 cell  = null;
				if(null!=inputStream)
				inputStream.close();
				if(null!=inputReader)
				inputReader.close();
				if(null!=bufferReader)
				bufferReader.close();
			} catch (IOException e) {
				Log.error("文件关闭异常！！！"+e.getMessage()+"任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］中");
				Log.error("忽略 ！！ 任务［"+dt.getName()+"］中文件［"+file.getAbsolutePath()+"］,进入下个文件");
			}
	
		}
		return _mapList;
	}
	
	
	
	
	
	public int synchroInsertData(DataTableBean dt) throws SQLException {
		int result = 0;
		this.isDataSources(dt.getSourceDataSource(), dt.getTargetDataSource());
		result = this.synchroInsertData(connSource, connTarget, dt);

		return result;
	}

	public int synchroUpdateData( DataTableBean dt) throws SQLException {
		int result = 0;
		this.isDataSources(dt.getSourceDataSource(), dt.getTargetDataSource());
		result = this.synchroUpdateData(connSource, connTarget, dt);

		return result;
	}
		  
	public int synchroInsertOrUpdateData(DataTableBean dt) throws SQLException {
		int result = 0;
		this.isDataSources(dt.getSourceDataSource(), dt.getTargetDataSource());
		result = this.synchroInsertOrUpdateData(connSource, connTarget, dt);
		return result;
	}
	
	
	public int synchroDeleteData(DataTableBean dt) throws SQLException {
		int result = 0;
		this.isDataSources(dt.getSourceDataSource(), dt.getTargetDataSource());
		result = this.synchroDeleteData(connSource, connTarget, dt);
		return result;
	}
}
